import json
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Font, Color, PatternFill
from openpyxl.styles import Border, Side
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter

# 假设我们有一些数据
data = {
    "name": "Alice",
    "age": 30,
    "city": "New York"
}

# 将数据转换为JSON格式的字符串
j_str = json.dumps(data)
print(j_str)

# 将数据写入JSON文件
with open('result.json', 'w') as fp:
    json.dump(data, fp)
# 从JSON文件中读取数据
with open("result.json") as f:
    d = json.load(f)
    print(d)

# 实例化一个新的工作簿
wb = Workbook()

# 获取当前活动的sheet
ws = wb.active

# 打印sheet表名
print(ws.title)

# 修改sheet名
ws.title = "salary luffy"

# 保存工作簿
wb.save('example.xlsx')
# 写入数据
ws['A1'] = "Name"
ws['B1'] = "Age"
ws['C1'] = "City"

ws['A2'] = "Alice"
ws['B2'] = 30
ws['C2'] = "New York"

# 保存工作簿
wb.save('example.xlsx')
# 选择特定的sheet
ws = wb['salary luffy']
# 保存工作簿
wb.save('example.xlsx')
# 按行遍历数据
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        print(cell.value)
    print()
# 按列遍历数据
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
    for cell in col:
        print(cell.value)
    print()

# 加载已有的Excel文件
wb2 = load_workbook('example.xlsx')

# 选择特定的sheet
ws2 = wb2['salary luffy']

# 遍历数据
for row in ws2.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        print(cell.value)
    print()

# 按行遍历数据
for row in ws2.rows:
    for cell in row:
        print(cell.value)
    print()

# 按列遍历数据
for col in ws2.columns:
    for cell in col:
        print(cell.value)
    print()

# 遍历指定⼏列的数据
# 取得第2-第5列的数据
for row in ws2.iter_rows(min_row=1, max_col=5, max_row=2):
    for cell in row:
        print(cell.value)
    print()

# 删除工作表
del wb['salary luffy']

# 实例化一个新的工作簿
wb3 = Workbook()

# 新建一个工作表
ws3 = wb3.create_sheet(title="salary3 luffy")

# 写入数据
ws3['A1'] = "Name"
ws3['B1'] = "Age"
ws3['C1'] = "City"

ws3['A2'] = "Alice"
ws3['B2'] = 30
ws3['C2'] = "New York"

# 保存工作簿
wb3.save('example3.xlsx')

# 设置单元格样式

# 设置字体
font = Font(name='Arial', size=12, bold=True)

# 设置单元格背景色
fill = PatternFill(fill_type='solid', start_color=Color('FFA07A'))

# 设置单元格前景色
fill = PatternFill(fill_type='solid', start_color=Color('FFFFFF'), end_color=Color('000000'))

# 设置单元格边框

border = Border(left=Side(border_style='thin', color='FF0000'),
                right=Side(border_style='thin', color='00FF00'),
                top=Side(border_style='thin', color='0000FF'),
                bottom=Side(border_style='thin', color='000000'))

# 设置单元格对齐方式

alignment = Alignment(horizontal='center', vertical='center')

# 设置单元格格式


# 设置单元格宽度

ws3.column_dimensions[get_column_letter(1)].width = 20
ws3.column_dimensions[get_column_letter(2)].width = 10
ws3.column_dimensions[get_column_letter(3)].width = 15

# 保存工作簿
wb3.save('example3.xlsx')
